package com.laundry.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import sun.jkernel.DownloadManager;

import com.laundry.lib.Database;
import com.laundry.lib.FreeSlot;
import com.laundry.lib.Reservation;
import com.laundry.lib.Slot;



public class Booking {

    public static Booking booking;

    private Booking() {
    }

    public static Booking instantiate() {
        if (booking == null) {
            booking = new Booking();
        }
        return booking;
    }

    public int bookASlot(Date date, int slot, String userId) {
        synchronized (this) {

            int availableMachineid = -1;
            int isBooked = -1;
            // check if the desired slot is available
            if(date==null)
            	System.out.print("**************  NULL ****************");
            else
            	System.out.print("**************"+date+"****************");
            java.sql.Date sqlDate = new java.sql.Date(Date.parse(date.toGMTString()));
            String checkSlotAvailable = "SELECT machineid FROM laundromat.laundry_machineslotavailabilty "
                    + " where date ='" + sqlDate.toString() + "' and slotid =" + slot + " and isbooked=0 "
                    + " order by slotid asc limit 1";
            try {
                Connection connection = Database.getConnection();
                Statement statement = connection.createStatement();

                ResultSet resultSet = statement.executeQuery(checkSlotAvailable);
                while (resultSet.next()) {
                    availableMachineid = resultSet.getInt(1);
                }
                connection.close();
                statement.close();
                // if yes then book
                if (availableMachineid != -1) {
                    //book, return booked machine id
                    Date now = new Date();
                    String book = " insert into  laundry_dailyreservations(UserId,MachineId,Date,Slot,HasUsed,Remarks,BookedDate) values( "
                        +"'"    + userId + "'," + availableMachineid + ",'" + sqlDate.toString() + "'," + slot + ",0,'booked','" + new java.sql.Date(Date.parse(now.toGMTString())) + "')";

                    String updateBookingStatus = "update  laundry_machineslotavailabilty set isbooked=1 where "
                            + " machineid=" + availableMachineid + " and slotid= " + slot + " and date='" + sqlDate.toString() + "'";

                    connection = Database.getConnection();
                    statement = connection.createStatement();
                    isBooked = statement.executeUpdate(book);

                    int rowsUpdated = statement.executeUpdate(updateBookingStatus);

                    statement.close();
                    connection.close();
                }

            } catch (Exception ex) {
                System.out.println(ex.getMessage());
            }
            return availableMachineid;
        }

    }

    public synchronized ArrayList<FreeSlot> getFreeSlots(Date date) {
        //get free slots and number of free machines and return it
        //HashMap<Slot, Integer> slotCount= new HashMap<Slot, Integer>();
        ArrayList<FreeSlot> freeSlots = new ArrayList<FreeSlot>();
        java.sql.Date sqlDate = new java.sql.Date(Date.parse(date.toGMTString()));
        String getFreeSlots = "SELECT slotid,masters.from,masters.to,count(*) as freeslots FROM laundry_machineslotavailabilty msa "
                + " join laundry_timeslotsmasterlist masters on msa.slotid=masters.id and isUsing=1 "
                + " where date='" + sqlDate.toString() + "' and isbooked=0 group by slotid";
        try {
            Connection connection = Database.getConnection();
            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(getFreeSlots);

            while (result.next()) {
                //slotCount.put(new Slot(result.getInt(1),result.getString(2),result.getString(3),true), result.getInt(4));
                freeSlots.add(new FreeSlot(new Slot(result.getInt(1), result.getString(2), result.getString(3), true), result.getInt(4)));
            }
            result.close();
            statement.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return freeSlots;
    }

    
    public ArrayList<Reservation> getMyBookings(String userId){
    	
    	ArrayList<Reservation> reservations=new ArrayList<Reservation>();
    	String myReservations="SELECT dr.bookingid , MachineId,ts.id as slotid,ts.from,ts.to,Date FROM laundry_dailyreservations dr "+
    	" join laundry_timeslotsmasterlist ts on dr.slot=ts.id "+
    	"where userId='"+userId+"' order by date desc";
    	try {
            Connection connection = Database.getConnection();
            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(myReservations);

            while (result.next()) {
                //slotCount.put(new Slot(result.getInt(1),result.getString(2),result.getString(3),true), result.getInt(4));
              reservations.add(new Reservation(result.getInt(1),userId,result.getInt(2),result.getDate(6),new Slot(result.getInt(3),result.getString(4),result.getString(5),true),true,""));  
            }
            result.close();
            statement.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    	
    	return reservations;
    }
   
    public synchronized void editBooking() {
    }

    public synchronized void deleteBooking() {
    }
    
    public synchronized int openBooking(Date date){
    	int numberOfRows=0;				
		java.sql.Date sqlDate= new java.sql.Date(Date.parse(date.toGMTString()));
		String insertQuery=		" insert into laundry_machineslotavailabilty select machine.machineid, slot.id,machine.date,0 from laundry_machines machine "+
		"join laundry_timeslotsmasterlist slot "+
		"where machine.isAvailabe=1 and machine.date='"+ sqlDate.toString() +"'";
		String dateCheckQuery=" select count(*) from laundry_machineslotavailabilty where  date =' "+sqlDate.toString()+"'";
		Connection conn=null;
		try {
		 conn= Database.getConnection();
		
		Statement st=conn.createStatement();
		ResultSet result=st.executeQuery(dateCheckQuery);
		
		result.next();
		int numberOfExistingEntries= result.getInt(1);	    
		result.close();
		conn.close();
		
		if(numberOfExistingEntries == 0){
			conn=Database.getConnection();
			st=conn.createStatement();
			numberOfRows= st.executeUpdate(insertQuery);			
		}
		else{
			String availableMachines=" SELECT machineId FROM laundry_machines l where isAvailabe=1 and date ='"+sqlDate+"'";
			String slots=" SELECT id FROM laundry_timeslotsmasterlist l where isUsing=1";
			
			conn=Database.getConnection();
			st=conn.createStatement();
			ResultSet availMach= st.executeQuery(availableMachines);
			//st.close();
			//conn.close();
			
			//conn=Database.getConnection();
			st=conn.createStatement();
			ResultSet availableSlots=st.executeQuery(slots);
			//st.close();
			//conn.close();
			
			
			String machineSlotExist="SELECT count(*) FROM laundry_machineslotavailabilty l where machineId=? and slotid=? and date=?";
			conn=Database.getConnection();
			while(availMach.next()){
				availableSlots.beforeFirst();
				while(availableSlots.next()){					
					PreparedStatement preparedStatement=conn.prepareStatement(machineSlotExist);
					preparedStatement.setInt(1, availMach.getInt(1));
					preparedStatement.setInt(2, availableSlots.getInt(1));
					preparedStatement.setDate(3, sqlDate);
					ResultSet doesExist= preparedStatement.executeQuery();
					doesExist.next();
					//if not existing
					if(doesExist.getInt(1)==0){
						//preparedStatement.close();
						doesExist.close();
						String insertRecord="insert into laundry_machineslotavailabilty values(?,?,?,?)";
						preparedStatement=conn.prepareStatement(insertRecord);
						preparedStatement.setInt(1, availMach.getInt(1));
						preparedStatement.setInt(2, availableSlots.getInt(1));
						preparedStatement.setDate(3, sqlDate);
						preparedStatement.setInt(4, 0);
						numberOfRows+=preparedStatement.executeUpdate();						
					}
					preparedStatement.close();
				}
			}
			
			availableSlots.close();
			availMach.close();
			conn.close();
		}
		}
		catch(Exception ex){
			ex.printStackTrace();		
		}
		finally{
			
		}
		
		return numberOfRows;
    }
}
